Spreadsheets

Format negative numbers without using complicated custom formats using formatting by example. Choose the cell you want to format, then choose Format/Cell and select the format category (we've selected Currency in our example). Then choose from the list of sample formats (which use real numbers for examples) so formatting negative numbers will be a positive experience.

Cut your data entry time in half with AutoComplete. When you begin typing in a cell, Excel 7 looks through all other rows to see what entries begin with the letters you've typed and automatically fills in a value when a match is found. To pick from a list of values without doing any typing, right-click on the cell and select "Pick from list..." Choose the value you want from the pulldown list.

To add up a group of numbers without creating a SUM function in a separate cell, highlight the cells and look at the status bar. AutoCalculate can show the Sum, Average, Count, Max, Min or the number of cells containing numbers. By default, Excel uses the Sum function to total the cells selected, but you can right-click on the calculated result to change the function.

Pick the top ?? percent of values in a spreadsheet with the new Top 10 AutoFilter. Click in any cell within the column you wish to filter. Select Data/Filter/AutoFilter and the cell displays a down-pointing arrow and becomes a pull-down list. Notice the new {Top 10...} option in this list. Choose it, then select the option(s) you wish, such as Top 5 percent or Bottom 20 Values.

To scroll great distances quickly, hold down the Shift key as you move the scroll bar thumbnail.

Get started with no learning curve by using one of the preformatted templates located under FILE/NEW/SPREADSHEET SOLUTIONS. You can quickly create invoices and purchase orders or even analyze car lease terms.

Allow multiple users to add data to the same worksheet at the same time by choosing FILE/SHARED LISTS/MULTI-USER.

Have data in Excel that you really need to put into a relational database? Due to very tight integration with Microsoft Access, you can use the Convert to Access Wizard to turn your spreadsheet into a true relational database just by following a few simple steps. This allows you to use the right tool for the right task.

Make a row bigger or smaller by dragging the bottom boundary of the row number to the desired location.To make a column wider or narrower, drag the right boundary of the column's heading. You can resize several rows or columns at once by selecting the row or column headers (click and drag across the numbers or letters), then dragging the boundary of any of the header cells.

Use the formatting commands to format a cell by adding text to the beginning. First highlight a cell containing a number. Then choose Format/Cells and fill in the code box with your custom number format. For example, to create a custom number format that will add the abbreviation "Dept." after a number, type # "Dept." in the code box. Now you can choose individual cells or groups of cells and apply your new custom number format to change the data.

Make your choices of borders and patterns stand out more vividly by turning off the gridlines. Select Tools/Options, then the View tab. Make sure there is no check mark in the Gridlines check box and click on OK.

If you're using your spreadsheet as a list keeper, you can quickly filter the rows so you see only those that contain a desired value. To set up a filtered list, select Data/Filter then choose AutoFilter. Now move to the column in the first row whose values you want to filter. Use the pull-down list to select the value, then press Enter. Excel displays only rows where the cell in the selected column contains the chosen value.

For spreadsheets used as lists, you can use an advanced filter option to hide duplicate records. Select Data/Filter, choose Advanced Filter and erase any value in the Criteria Range text box. Check the Unique Records Only and click on OK.

Many users encounter errors when they select cells and try to sort them. For example, when sorting an entire worksheet, you may be tempted to select the column you want to sort, then use the sort command. Unfortunately, the adjacent cells aren't sorted with your data, so values are "scrambled." Undo won't be reliable for large spreadsheets. In case your sort fails to work the way you expect, save your worksheet before you perform a sort.

To return to a cell or named range you recently worked on, click inside the cell name box in the upper left corner below the font dropdown box. Type in the cell name you want or click on the drop-down arrow to the right of the box. Choose the named range you want to move to.

Having trouble seeing those small toolbar icons? Choose View/Toolbars, then select Large Buttons. You'll be able to see the new buttons comfortably.

If you want the convenience of 3-D workbooks, but prefer the menus in Excel 4.0, select Tools/Options/General, then choose Microsoft Excel 4.0 menus.

To create a custom autofill list, type in the list you wish to add, with each entry in a separate, adjacent cell. Select the cells and choose Tools/Options/Custom Lists and press the Import button, then choose OK. To use your list, type the first word of the series, then (assuming you have cell drag-and-drop enabled--see related tip) grab the handle on the bottom right of the first cell and drag the mouse until the cells you want filled are highlighted.

Name a worksheet by double-clicking on its tab and typing the tab's name. To rearrange your worksheets, drag and drop the worksheet's tab to its new location among the existing tabs. Select multiple tabs the same way you would files in File Manager: Press Shift+Click for contiguous tabs, and Ctrl+Click for noncontiguous tabs.

Make color and border palettes available at all times by "tearing off'' the selection boxes and moving them around on the desktop. Click on the drop-down arrow with your left mouse button and keep the mouse button pressed, then drag the item to its new location. You can do this with any button bar icon that has a drop-down palette.

If you want your new workbooks to have more (or fewer) worksheets than the default (16), select Tools/Options/General, then type the number of sheets you want (from 1 to 255) in the Sheets in New Workbook selection.

The number underlying a cell (123.1, for example) and the number actually displayed in a cell (such as 123) may differ due to rounding or truncation imposed by the format you've selected. To make the underlying value of any cell identical to the number displayed, select Tools/Options/Calculation and select Precision as Displayed. Be careful: The accuracy of large spreadsheets with many calculations may be compromised if you use this option.

To add a new series to your chart, select the cells containing the new series of data to be displayed, then drag and drop it onto an existing chart. Excel redraws the chart with your new data included.

Drag-and-drop is a standard feature of many programs, but your system administrator (or the person using your machine while you're out to lunch) may have turned it off because it often confuses users who have worked with earlier versions of Excel or different spreadsheet programs. To turn it on, choose Tools/Options/ Edit and select Allow Cell Drag and Drop.

To copy a *formula* from the cell above your current position, press Ctrl+'. To copy the *value* of the cell above your current position, press Ctrl+".

To change the graphic on a toolbar button, right-click on the toolbar, then select Customize. While the Customize dialog box is open, right-click on any current toolbar button (*not* the buttons displayed in the Customize dialog box). Choose Edit Button Image from the pop-up menu and Excel displays the Button Editor. Alternatively, you can copy an image or assign a macro to the button. You can also make these selections by right-clicking on a toolbar icon in the Customize dialog box.

Use a template for frequently used forms. Build the framework of the worksheet, then save it using File/Save As. Choose Template from the Save File as Type list and save it in the XLSTART subdirectory. To use the template as the basis of a new workbook, choose File/New and select the template name.

Use matrix math to increase a range of cells quickly (to increase all cells by 10 percent, for example). Enter the value 1.10 in a blank cell, such as A1. Choose Range/Analyze/Multiply Matrix. Choose the range you want to update (such as B1..B50) as the first matrix. Enter the cell containing the new factor (cell A1 in this example), and enter B1..B50 for the output range. When you select OK, this command multiplies all cells in the range by 1.10 and replaces each cell with the calculated value.

Quickly change the format of a cell by choosing the Style box from the Status Bar (at the bottom of the screen). The list displays all number formats available. To shorten this list, select Style/Number Format. Highlight each format you want to appear in the list, and check the Show in status bar box.

Highlight the block of cells surrounding your current cell (that is, the rectangular area surrounded by a blank row or column) by pressing Ctrl+*.

To move to the last non-blank cell in a column, it pays to go down, then up. Select the current column by clicking on the column header. Move to the bottom of the column by pressing Shift+Enter. Then move up to the last non-blank cell in the column by pressing End+UpArrow. Author changed info.

Create a running total of cells adjacent to a column of cells by assuming the original column of cells begins in cell A1 and runs through cell A100. In cell B1, enter the formula @SUM($A$1..A1), then copy the formula to cells B2 through B100.

To hide a worksheet, choose Style/Hide, then choose Sheet. To display a hidden worksheet, choose Style/Hide, then check Sheet. Enter a range that includes cells on the worksheets before *and* after the hidden worksheet. Click on Show to see the worksheet.

Here's a fast way to compile summary reports from several worksheets or workbooks without retyping or cutting and pasting. Move to the cell(s) in which you want the summary information to appear, then choose Data/Consolidate. You'll see a series of dialog boxes in which you'll choose the mathematical function you wish to use. You can select up to 255 separate source references, which can be named areas or cell references on any worksheet or workbook accessible from your desktop, such as those on your network. Choose the Create Links to Source Data option. Consolidation is updated automatically when the source data changes.

1-2-3 ships with eight standard toolbars. To change a toolbar, choose Tools/SmartIcons. The dialog box 1 lets you change icon sizes, assign macros to icons and edit the icons themselves. Toolbars can be anchored on any side or your screen or left "floating" around your screen.

Buttons on the Printing Toolbar make for easier printing. To access the toolbar, click on the far right icon (Select the Next Set of SmartIcons), or choose Tools/ SmartIcons and select Printing. This toolbar puts the important print controls at your fingertips, including options to Set Columns as Print Titles and Size Data to Printed Page.

To insert a cell into your worksheet, select the cell that will be immediately below or to the right of the new cell, then press Ctrl+ (the plus sign on the numeric keypad), and check Insert Selection. To delete a cell, select the cell, press Ctrl- (the minus sign) and choose the Delete Selection check box.

To move data between worksheets in the same file, highlight the contents you wish to move. While the mouse button is still depressed, point at the tab for the destination worksheet, then point at the top left corner of the area you wish to fill with data. If you are working with separate files, this procedure will copy, rather than move, the data. To copy data to another worksheet in the same file, press the Ctrl key and proceed as above.

Can't figure out what's wrong with a formula? Try the Sheet Auditing toolbar by selecting Tools/SmartIcons/Sheet Auditing. Among its features are special functions for formulas, formula precedents, cell dependencies, DDE links and links to 1-2-3 files. It can also help you locate the next cell adjoining a blank cell in any direction.

Move around your spreadsheet in a hurry with the Navigator. The button is to the right of the formula bar and to the left of the Function icon. Press it and you'll see a list of Named Ranges. Choose the Named Range that you wish to move to, and you're there.

Worksheet tabs now support Fill by Example. Double-click on the worksheet tab, then type in the first value in the series, such as January. As you click on New Sheet, each additional worksheet tab contains the name of the next month in the series.

To create a custom list for use with Fill by Example, use Notepad or another text processor to edit the FILL.INI file, located in 1-2-3's PROGRAM directory. Follow the example of the series already included to create new sets. For example, create a new set by moving to the end of the file and typing the following:

[SET x]<Enter>

ITEM3=Advertising

and so on. Number the new set with the next highest number. Include as many items in the series as you want. The lists serves as the basis of a fill-by-example command when the first item (Sales in this example) is used as the first element.

To insert columns, rows or worksheets, press the Ctrl key and the plus sign on the numeric keypad, then choose the type of insertion from the dialog box. To delete columns, rows or worksheets, press Ctrl and the minus key, then choose what you want to delete from the dialog box.

1-2-3's Control Panel contains an icon with the @ symbol. When you click on it, you'll see a list of the most frequently used formulas and an option that allows you to see all formulas. The dialog box gives a brief description of each formula as you scroll down the list. Choosing a formula from the @function pull-down list inserts the formula in the current cell and prompts you for variables that the formula needs.

To format a range of cells in a hurry, select your range, choose Style/Gallery and select any one of 14 predesigned styles. Move the cursor down the list of choices to preview the new format.

Use SmartMasters, specialized templates with dozens of formatting options predefined, to get a head start on data analysis. When you create a new document with the File/New command, you will see a list that includes templates for time sheets, expense reports, mortgage amortizations and other documents. The Shell template can even assist you in creating your own custom templates.

Want to generate a custom map of your company's sales performance by state? Your data must use the standard two-letter Postal Service abbreviations. Select the states with the numerical information in the row or column beside them, and choose Tools/Map/Insert Map Object. Your cursor changes to a globe with crosshairs. Click on the upper-right corner of the spot where you want the map to be placed. 1-2-3 generates a map color, coded to the numerical data you've selected.

To share your work with others in your workgroup, select the range you wish to mail to them, then choose File/Send Mail and follow the prompts. You can create a version before you send it, if you wish to take advantage of 1-2-3's Version Manager technology. The range can be routed concurrently or sequentially, and you'll be able to see who made which comments. To combine the data received from different sources, use the program's Merge feature.

Add nonprinting comments to cells by adding a semicolon, followed by the comment text, at the end of a formula or value, such as
8.50; current annual percentage rate
.

Compute a value before entering it into a cell by entering the cell formula and pressing F9. The value is displayed. Press Enter and the value will be placed in the current cell.

Press F2 to switch to edit mode, where you can use the left and right arrow keys to move through the characters in an entry. To delete all data on the edit line, press Ctrl+Backspace. To erase all data to the right of the cursor, press Ctrl+\.

To enter dates that adjust for the varying number of days each month, accounting for leap years, enter the beginning date in the first cell by pressing Ctrl+Shift+D. In the cell directly beneath or to the right of this cell, enter this formula, all on one line:

+A1+@CHOOSE(@MONTH(A1)-1),31,@IF(@MOD(@YEAR(A1),4)=0 #AND#@MOD(@YEAR(A1),100)0#OR#@YEAR(A1)=100,29,28,31,30,31,30,31,31,30,31,30,31)

Copy this formula to the remaining cells in the row or column, then format these cells as dates.

Check if a cell (such as cell A1) is blank with this formula:
@IF(@CELL("type",A1)="b",1,0)
. To see if a cell contains a value, substitute "v" for "b". To check if the cell contains a label, replace "b" with "l". You can also check for blanks in cell A1 by using the formula
@IF(A1="",1,0)